#初始化参数信息,backupDBList 需要备份的数据库名称，支持多个路径用;号分割，RunsPath清理脚本所放置的路径
RunsPath="/data"
backupDBList="ngs_db;ngs_ph"
Uusername="root"
Upassword="sdkjds13F~"
backupPathList="/data/database_bak"

export MySQL_HOME=/usr/local/mysql
export PATH=$MySQL_HOME/bin:$PATH

# 保留文件数
ReservedNum=3
echo "###`date "+%Y-%m-%d %H:%M:%S"`系统自动备份数据库脚本启动...【备份DB】:"$backupDBList" 【运行脚本所在路径】:"$RunsPath >> $RunsPath'/'autoBackupMySQL.log
#以上为配置信息-以下内容不需要修改

backupFileNo=`date +%Y%m%d_%H%M%S`
ReservedNum=$((${ReservedNum}+1))
echo '保留文件数:'${ReservedNum}
backupDBArr=(${backupDBList//;/ })

loopNum=0
for backupDB in ${backupDBArr[@]}
do
  loopNum=$(($loopNum+1))
  echo $loopNum'-当前待备份数据库:【'${backupDB}'】' >> $RunsPath'/'autoBackupMySQL.log

  backupFileName=${backupDB}'_backup'${backupFileNo}

  # 备份
  # mysqldump -u${Uusername} -p${Upassword} ${backupDB} --force > $RunsPath'/'${backupFileName}.sql

  # 备份&压缩
  mysqldump -u${Uusername} -p${Upassword} ${backupDB} --force | gzip > $RunsPath'/'${backupFileName}.sql.gz #备份&压缩

  echo $loopNum'-【'${backupDB}'】数据库备份完成' >> $RunsPath'/'autoBackupMySQL.log

  # 将备份文件复制到备份文件夹
  backupPathArr=(${backupPathList//;/ })
  for backupPath in ${backupPathArr[@]}
  do
    cp $RunsPath'/'${backupFileName}.sql.gz ${backupPath}
    cd ${backupPath}
    ls -t |grep ${backupDB}'_backup' | sed -n ''${ReservedNum}',$p' | xargs -I {} rm -rf {}
    cd ${RunsPath}
    echo $loopNum'-【'${backupDB}'在'${backupPath}'】历史备份清理完成' >> $RunsPath'/'autoBackupMySQL.log
  done

  # 删除备份临时文件
  rm -rf $RunsPath'/'${backupFileName}.sql.gz
  echo $loopNum'-【'${backupDB}'】备份临时文件清理完成' >> $RunsPath'/'autoBackupMySQL.log

done

#将清理结果记录到历史文件
echo Run at `date "+%Y-%m-%d %H:%M:%S"` Succ... >> $RunsPath'/'autoBackupMySQL.log

autobackupDBName=$(basename $0)
echo "当前备份数据库路径:"$RunsPath'/'$autobackupDBName

#设置每天晚上11点半启动执行删除脚本
echo "自动检查&设置定时器..."
if [ ! -e /var/spool/cron/$USER ];then
	mkdir -p /var/spool/cron/
	touch /var/spool/cron/$USER
	echo "定时器文件创建完成..."
fi

if [ `grep -v '^\s*#' /var/spool/cron/$USER |grep -c $autobackupDBName` -eq 0 ];then
  # 每天晚上11:30自动清理日志的时间表达式:30 23 * * *
	echo "30 23 * * *"' '$RunsPath'/'$autobackupDBName >> /var/spool/cron/$USER
	echo "已经将任务写入定时器..."
fi